package office.excel.poi.exp;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;
import java.util.Set;
import javax.servlet.http.HttpServletRequest;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import office.excel.poi.utils.CellUtil;
import office.utils.StringUtil;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

/**
 * 
 * @author zhdy
 *注意事项：在使用poi写的过程中 发现如果不给cell设置格式 则该cell会为空
 */
public class ExpExcel extends LoadExcel{
	
	
	
	private static HttpServletRequest request;
	public static File excelTemplateFile;
	public static File newExcelFile;
	//提交的json的Excel名称  与 expExcelUtil.js，expExcel函数的 data中参数对应
	private static String REQ_NAME="name";
	//提交的json的开始写的列名称 与 expExcelUtil.js，expExcel函数的 data中参数对应
	private static String REQ_BCNAME="bcName";
	//提交的json的开始写的列名称 与 expExcelUtil.js，expExcel函数的 data中参数对应
	private static String REQ_BRNAME="brName";
	//提交的json的table的数据名称 与 expExcelUtil.js，expExcel函数的 data中参数对应
	private static String REQ_TABJSON="tabJson";
	//提交的json的其他的数据名称  与 expExcelUtil.js，expExcel函数的 data中参数对应
	private static String REQ_OTHJSON="othJson";
	//与expExcelUtil.js 的 tdKey对应
	private static String TD_KEY="text";
	/**
	 * 第一个行列列标题
	 * 找到开始写入的索引
	 */
	public static String ExcelTemplateBeginCellFlag;
	public static String ExcelTemplateBeginRowFlag;
	public static Integer beginRow;
	public static Integer beginColumn;
	public ExpExcel(HttpServletRequest request)
	{
	    ExpExcel.request = request;
	    LoadExcel.ExcelTemplateName = request.getParameter(REQ_NAME);
	    //新的 Excel 加上当前时间
	    LoadExcel.NewExcelName = System.currentTimeMillis()+ExcelTemplateName;
	    //开始写的列名
	    this.ExcelTemplateBeginCellFlag = request.getParameter(REQ_BCNAME);
	    this.ExcelTemplateBeginRowFlag = request.getParameter(REQ_BRNAME);
	    
	}
	private static void init(HSSFSheet sheet)
	{
		if(!StringUtil.isEmpty(ExcelTemplateBeginCellFlag))
		{
			beginRow = CellUtil.getBeginRowIndex(sheet,ExcelTemplateBeginCellFlag);
			beginColumn = CellUtil.getBeginColumnIndex(sheet,ExcelTemplateBeginCellFlag);
		}
		else if(!StringUtil.isEmpty(ExcelTemplateBeginRowFlag))
		{
			beginRow = CellUtil.getBeginRowIndex(sheet,ExcelTemplateBeginRowFlag);
			beginColumn = CellUtil.getBeginColumnIndex(sheet,ExcelTemplateBeginRowFlag);
		}	
	}

	/**
	 * 通过jsonArray导出Excel
	 * 
	 * @return
	 * @throws Exception 
	 */
	public static String expExcelByJsonArray() throws Exception 
	{
		String path = "";
		Object objTabJosn = request.getParameter(REQ_TABJSON);
		Object objOthJosn = request.getParameter(REQ_OTHJSON);
		
		JSONArray jsonArray =JSONArray.fromObject(objTabJosn);
		JSONObject jsonObject = JSONObject.fromObject(objOthJosn);
		
		HSSFWorkbook templateWorkbook = null;
        HSSFSheet sheet = null;
        
        excelTemplateFile = LoadExcel.loadExcelTemplate();
        File file = new File(LoadExcel.basePath+LoadExcel.NewExcelPath);
        if(!file.exists())file.mkdirs();
        FileOutputStream  fos  = new FileOutputStream(LoadExcel.basePath+LoadExcel.NewExcelPath+LoadExcel.NewExcelName);
        
		try {
			
			templateWorkbook = new HSSFWorkbook(new FileInputStream(excelTemplateFile));
			sheet = templateWorkbook.getSheetAt(0);
			init(sheet);
			
			HSSFRow row ;
			HSSFCell column;  
			HSSFCellStyle style;
		for (int i = 0; i < jsonArray.size(); i++) {
			
			    
				JSONArray array = JSONArray.fromObject(jsonArray.get(i));
				
				for (int j = 0; j < array.size(); j++) {
					
					if(!StringUtil.isEmpty(ExcelTemplateBeginCellFlag))
					{
						
						row = sheet.getRow(beginRow+i+1);
						if (row==null)row=sheet.createRow(beginRow+i+1);
						column = row.getCell(beginColumn+j);
						if (column==null)column=row.createCell(beginColumn+j);
						String v = array.getString(j);
						
				        style = sheet.getRow(beginRow+1).getCell(beginColumn+j).getCellStyle();
				        column.setCellStyle(style);
			
					    column.setCellValue(v);
					}else if(!StringUtil.isEmpty(ExcelTemplateBeginRowFlag))
					{
						row = sheet.getRow(beginRow+i);
						column = row.getCell(beginColumn+j+1);
					    column.setCellValue(array.getString(j));
					}				
					
				}
			}
			Set<?> set = jsonObject.keySet();
			Iterator<?> it = set.iterator(); 
			while (it.hasNext()) {
				Object objKey = it.next();
				String value = jsonObject.get(objKey).toString();
				int rowIndex = CellUtil.getBeginRowIndex(sheet, objKey.toString());
				int colIndex = CellUtil.getBeginColumnIndex(sheet,objKey.toString());
				if(rowIndex >=0 && colIndex >= 0){
					  row = sheet.getRow(rowIndex);
					   column = row.getCell(colIndex+1);
					  column.setCellValue(value);
				}
			}
		
			templateWorkbook.write(fos);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			fos.flush();
			fos.close();
		}
		return path;
	}
}
